package controle.dao.cadastro.endereco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import processos.banco.CriaConexao;
import controle.entidade.cadastro.ClientePJuridica;
import controle.entidade.endereco.Logradouro;
import controle.entidade.prinicipal.Login;

public class LogradouroDao {
	
	private Connection conexao;

    public LogradouroDao() throws SQLException, IOException {
        this.conexao = CriaConexao.getConexao();

    }
             // Metodo de inserção em banco de dados mysql
    public void adicionar(Logradouro c1) throws SQLException {
        String sql = "insert  into tb_logradouro("
                + "fi_substancia) values(?)";
        
        PreparedStatement stmt = conexao.prepareStatement(sql);

        stmt.setString(1, c1.getLogradouro());
        
        stmt.executeUpdate();
        stmt.close();

    }

    public void excluir(Logradouro c1) throws SQLException {
        
        String sql = "delete from tb_logradouro where fi_pk_id= ?";
        
        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setInt(1, c1.getId());
        
        stmt.execute();
        stmt.close();
    }

    public void alterar(Logradouro c1) throws SQLException {
        String sql = "UPDATE tb_substancias SET "
                + "fi_substancia=?"
                +"WHERE fi_pk_id=?";
        
        PreparedStatement stmt = conexao.prepareStatement(sql);
        
                  // setar valores
           
        stmt.setString(1, c1.getLogradouro());
        stmt.setInt(2, c1.getId());
        
        
        stmt.executeUpdate();
        stmt.close();

    }

    
    
    
    
    public List<Logradouro> getLista(String userName) throws SQLException{
        
    String sql = "select * from tb_usuario WHERE fi_substancia like ? ORDER BY fi_substancia ASC";
    PreparedStatement stmt = this.conexao.prepareStatement(sql);
    stmt.setString(1, userName);
    //stmt.setString(2, passWord);
    ResultSet rs = stmt.executeQuery();
    
   
    List <Logradouro> listaLogradouros = new ArrayList<Logradouro>();
    
    while (rs.next()){
     
    	Logradouro c1 = new Login();
    	c1.setLogradouro(rs.getString(""));
    	c1.setId(    );
               // c1.setFi_id(Integer.valueOf(rs.getString("fi_pk_id")));
              //  c1.setSubstancia(rs.getString("fi_substancia"));
                
                 
        listaLogradouros.add(c1);
    }    
    rs.close();
    stmt.close();
   
    return listaLogradouros;
     
    }
    
    
    public ArrayList<Logradouro> getLista() throws SQLException{
        
    	String sql = "select fi_substancia from tb_substancias";  
        PreparedStatement stmt = this.conexao.prepareStatement(sql);  
        ResultSet rs = stmt.executeQuery();  
  
        List<LogradouroDao> minhalista = new ArrayList<LogradouroDao>();  
  
        while (rs.next()){  
  
        	Logradouro cad = new Logradouro();  
            cad.setLogradouro(rs.getString("fi_logradouro"));//(rs.getString("nome"));  
            minhalista.add(cad);  
        }  
        rs.close();  
        stmt.close();  
    
        return (ArrayList<Logradouro>) minhalista;  
    }

	
	
	

}
